IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CleanupProtocolBoardReviews]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CleanupProtocolBoardReviews]
GO
/****** Object:  StoredProcedure [dbo].[CleanupProtocolBoardReviews]    Script Date: 10/14/2008 14:27:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Irina Kozlova
-- Create date: 10/14/2008
-- Description:	Deletes records from ProtocolBoardReview table in Insight for the board review statuses 
-- that were deleted in Res; to be called by Human feed
-- =============================================
CREATE PROCEDURE dbo.CleanupProtocolBoardReviews
AS

BEGIN
	SET NOCOUNT ON

	IF (SELECT COUNT(*) FROM Stg_HumanBoardReviewIDs) > 50000
		BEGIN
			BEGIN TRY
			BEGIN TRANSACTION DeleteBoardReviews

			DELETE FROM protocolboardreview
			WHERE ID NOT IN (SELECT BoardReviewID FROM Stg_HumanBoardReviewIDs)
				AND IsAnimalBR = 0

			COMMIT TRANSACTION DeleteBoardReviews
			END TRY

		BEGIN CATCH
			DECLARE 
			@ErrorMessage    NVARCHAR(4000),
			@ErrorNumber     INT,
			@ErrorSeverity   INT,
			@ErrorState      INT,
			@ErrorLine       INT,
			@ErrorProcedure  NVARCHAR(200);

		-- Assign variables to error-handling functions that 
		-- capture information for RAISERROR.
		SELECT 
			@ErrorNumber = ERROR_NUMBER(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE(),
			@ErrorLine = ERROR_LINE(),
			@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

		-- Building the message string that will contain original
		-- error information.
		SELECT @ErrorMessage = 
			N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
				'Message: '+ ERROR_MESSAGE();
	
		-----------------------------------------------------
		-- ROLLBACK the transaction if we got here....
		-----------------------------------------------------
		IF XACT_STATE() <> 0 Rollback transaction DeleteBoardReviews

		-- Raise an error: msg_str parameter of RAISERROR will contain
		-- the original error information.
		RAISERROR 
			(
			@ErrorMessage, 
			@ErrorSeverity, 
			1,               
			@ErrorNumber,    -- parameter: original error number.
			@ErrorSeverity,  -- parameter: original error severity.
			@ErrorState,     -- parameter: original error state.
			@ErrorProcedure, -- parameter: original error procedure name.
			@ErrorLine       -- parameter: original error line number.
			);
			return 99
		END CATCH;
		END


	SET NOCOUNT OFF
END

GO